import time
import numpy as np
import pymysql
import pandas as pd
from datetime import datetime
import pymysql.cursors
import os


class MysqlUtils(object):
    def __init__(self, *args):
        self.conn = pymysql.connect(
            host='localhost',
            user="root",
            passwd="sjk1234",
            db="sys",
            port=3306,
            charset="utf8"
        )
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)  # 使用字典游标方便后续处理

    def get_scenic_data(self):
        sql = """
        SELECT 
            order_id, 
            id_no, 
            user_name, 
            phone, 
            age, 
            user_count
        FROM (
            SELECT 
                o.id AS order_id, 
                u.id_no, 
                u.user_name, 
                u.phone,
                CASE
                    WHEN LENGTH(u.id_no) = 18 THEN YEAR(NOW()) - CAST(SUBSTR(u.id_no, 7, 4) AS SIGNED)
                    ELSE NULL
                END AS age,
                (SELECT COUNT(*) FROM ticket_order_user_rel WHERE order_id = o.id) AS user_count
            FROM 
                ticket_order o 
            JOIN 
                ticket_order_user_rel u ON u.order_id = o.id
            WHERE 
                u.id_no IS NOT NULL 
                AND u.id_no != ''
        ) AS subquery
        WHERE 
            user_count = 1 
            AND (age < 18 OR age >= 60);
        """
        try:
            self.cursor.execute(sql)
            ret = self.cursor.fetchall()
            df = pd.DataFrame(list(ret))
            print(df.head())
            output_dir = './outlier'
            if not os.path.exists(output_dir):
                os.makedirs(output_dir)
            df.to_csv(os.path.join(output_dir, 'scenic_data.csv'), index=False)
        except Exception as e:
            print(f"查询或保存数据时发生错误: {e}")
        finally:
            self.cursor.close()
            self.conn.close()


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()